package com.lc.demo.mapper;

import com.lc.demo.bean.Course;
import com.lc.demo.bean.Student;
import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import tk.mybatis.mapper.common.BaseMapper;

import java.util.List;

public interface CourseMapper extends BaseMapper<Course> {

    //往关系表中插入数据

    @Insert("insert into grade_course  values(#{classgradeid},#{courseid})")
    void  insertGradeCourse (@Param("classgradeid") Integer classgradeid, @Param("courseid") Integer courseid);


    @Delete("delete from grade_course where courseid = #{courseid}")
    void  delGradeCourse (@Param("courseid") Integer courseid);

    @Delete("delete from leavetable where courseid =#{id}")
    void deleteSignin(Integer id);
    @Delete("delete from signin where courseid =#{id}")
    void deleteLeavetable(Integer id);

    @Select("SELECT\n" +
            "\tt1.* \n" +
            "FROM\n" +
            "student t1 LEFT JOIN classgrade t2 on t1.classid=t2.id\n" +
            "LEFT JOIN grade_course t3 on t2.id=t3.classgradeid left join signin t4 on t3.courseid=t4.courseid\n" +
            " \n" +
            " where t4.courseid=#{id} and t4.state=1")
    List<Student> selectSignPerson(Integer id);


    @Select("SELECT\n" +
            "\tt1.* \n" +
            "FROM\n" +
            "student t1 LEFT JOIN classgrade t2 on t1.classid=t2.id\n" +
            "LEFT JOIN grade_course t3 on t2.id=t3.classgradeid left join signin t4 on t3.courseid=t4.courseid\n" +
            " \n" +
            " where t4.courseid=#{id} and t4.state=0")
    List<Student> selectNoSignPerson(Integer id);



    @Select(" select\n" +
            "    t1.*\n" +
            "    from \n" +
            "    student t1\n" +
            "\n" +
            "    LEFT JOIN classgrade t2 on t1.classid=t2.id\n" +
            "    LEFT JOIN grade_course t3 on t2.id=t3.classgradeid\n" +
            "    LEFT JOIN course t4 on t4.id=t3.courseid\n" +
            "    where t4.id=#{courseid}")
    List<Student> selectCourseStudentList(@Param("courseid") Integer courseid);
}
